package com.chacha.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;

import com.chacha.framework.common.models.DashboardObject;
import com.chacha.framework.common.models.DashboardValue;
import com.chacha.framework.common.models.PagingObject;
import com.chacha.framework.common.models.SortObject;

public class BaseDao {
	
	protected DataSource getDataSource(String jndi) {
		Context ctx;
		try {
			ctx = new InitialContext();
			return (DataSource) ctx.lookup(jndi);
		} catch (NamingException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public List<DashboardObject> getStandartList(String jndi, String tableName, List<DashboardValue> selectKeyword, List<DashboardValue> keywords, PagingObject paging) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM "+tableName+"  WHERE 1=1 ");
		if(keywords !=null ){
			if(keywords.size()>0){
				sql.append(" AND ( ");
				String prefix="";
				for(DashboardValue val: keywords){
					if(val.getValue()!=null){
						sql.append(prefix);
						prefix=" OR ";
						sql.append(val.getNameValue()+" LIKE '%"+val.getValue()+"%' " );
					}
				}
				
				sql.append(" )");
			}
			
		}
		
		for(DashboardValue val: selectKeyword){
			if(val.getValue()!=null){
				if(!"".equals(val.getValue()) && !val.getValue().toString().isEmpty())sql.append(" AND "+val.getNameValue()+"='"+val.getValue()+"'");
			}
		}
		
		if(paging !=null){
			if(paging.getSorts() != null && !paging.getSorts().isEmpty()){
				String sort = " order by ";
				for(SortObject sortObject: paging.getSorts()){
					sort += sortObject.getField() + " " + sortObject.getSortType().getValue() + " ,";
				}
				sql.append(StringUtils.removeEnd(sort, ","));
			}
			
			if(paging.getLastRow()>0){
				sql.append(" LIMIT " +paging.getFirstRow()+","+paging.getLastRow());
			}
			
		}
		System.out.println("getStandartList: "+sql);
		return proceedQueryMultiple(jndi, sql);
	}
	
	public int getStandartListCount(String jndi, String tableName, List<DashboardValue> selectKeyword, List<DashboardValue> keywords) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT count(*) as total FROM "+tableName+"  WHERE 1=1 ");
		
		if(keywords !=null){
			sql.append(" AND ( 1=1 ");
			for(DashboardValue val: keywords){
				if(val.getValue()!=null){
					sql.append(" OR "+val.getNameValue()+" LIKE '%"+val.getValue()+"%' " );
				}
			}
			
			sql.append(" )");
		}
		
		for(DashboardValue val: selectKeyword){
			if(val.getValue()!=null){
				sql.append(" AND "+val.getNameValue()+"='"+val.getValue()+"'");
			}
		}
		
		int test=0;
		Connection con = null;
		try {
	
			con = ConnectionUtil.getConnection(jndi);
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
	
			if (rs.next()) {
				test = rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return test;
	}
	
	public List<DashboardValue> getSingleRow(String jndi, String tableName, List<DashboardValue> selectKeyword) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM "+tableName+"  WHERE 1=1 ");
		
		for(DashboardValue val: selectKeyword){
			if(val.getValue()!=null){
				sql.append(" AND "+val.getNameValue()+"='"+val.getValue()+"'");
			}
		}
		
		return proceedQuerySingle(jndi, sql);
	}
	
	protected int insertTable(Connection con, String tableName, List<DashboardValue> datas) throws SQLException{
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO "+tableName);
		String col=" (";
		String values=" VALUES(";
		for(DashboardValue val:datas){
			col+=val.getNameValue()+",";
			values+=val.getValue()+",";
		}
		col=col.substring(0, col.length()-1)+") ";
		values=values.substring(0, values.length()-1)+") ";
		sql.append(col);
		sql.append(values);
		
		Statement st = con.createStatement();
		int result = st.executeUpdate(sql.toString());
		st.close();
		
		return result;
	}
	
	protected int updateTable(Connection con, String tableName, List<DashboardValue> datas, List<DashboardValue> where) throws SQLException{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE "+tableName);
		String set=" SET ";
		for(DashboardValue val:datas){
			set+=val.getNameValue()+"="+val.getValue()+" ,";
		}
		set=set.substring(0, set.length()-1)+") ";
		
		String setCondition=" WHERE ";
		for(DashboardValue val:datas){
			setCondition+=val.getNameValue()+"="+val.getValue()+" AND";
		}
		setCondition=setCondition.substring(0, setCondition.length()-3)+") ";
		
		sql.append(set);
		sql.append(setCondition);
		
		Statement st = con.createStatement();
		int result = st.executeUpdate(sql.toString());
		st.close();
		
		return result;
	}
	
	protected int deleteFromTable(Connection con, String tableName, List<DashboardValue> datas, List<DashboardValue> where) throws SQLException{
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM "+tableName);
		
		
		String setCondition=" WHERE ";
		for(DashboardValue val:datas){
			setCondition+=val.getNameValue()+"="+val.getValue()+" AND";
		}
		setCondition=setCondition.substring(0, setCondition.length()-3)+") ";
		
		sql.append(setCondition);
		
		Statement st = con.createStatement();
		int result = st.executeUpdate(sql.toString());
		st.close();
		
		return result;
	}
	
	protected List<DashboardObject> proceedQueryInDashboardObject(Connection con, String sqlQuery) throws SQLException {
		List<DashboardObject> result = new ArrayList<>();
		PreparedStatement ps = con.prepareStatement(sqlQuery);
		ResultSet rs = ps.executeQuery();
		DashboardObject obj = null;
		List<DashboardValue> values = null;
		DashboardValue value = null;
		while (rs.next()) {
			ResultSetMetaData metadata =  rs.getMetaData();
			obj = new DashboardObject();
			values = new ArrayList<DashboardValue>();
			for (int x = 0; x < metadata.getColumnCount(); x++) {
				value = new DashboardValue(metadata.getColumnLabel(x + 1), rs.getObject(x + 1));
				values.add(value);
			}
			obj.setValues(values);
			result.add(obj);
		}
		return result;
	}

	protected List<HashMap<String, Object>> proceedQueryInHashMap(Connection con, String sqlQuery) throws SQLException {
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
		PreparedStatement ps = con.prepareStatement(sqlQuery);
		ResultSet rs = ps.executeQuery();
		HashMap<String, Object> row = null;
		while (rs.next()) {
			ResultSetMetaData metadata =  rs.getMetaData();
			row = new HashMap<String, Object>();
			for (int x = 0; x < metadata.getColumnCount(); x++) {
				row.put(metadata.getColumnLabel(x + 1), rs.getObject(x + 1));
			}
			result.add(row);
		}
		return result;
	}
	
	
	protected List<DashboardObject> proceedQueryMultiple(String jndi, StringBuilder sql) {
	    	
			Connection con = null;
			List<DashboardObject> objs = new ArrayList<>();
			try {
	
				con = ConnectionUtil.getConnection(jndi);
				PreparedStatement ps = con.prepareStatement(sql.toString());
				ResultSet rs = ps.executeQuery();
	
				DashboardObject obj = null;
				List<DashboardValue> values = null;
				DashboardValue value = null;
				while (rs.next()) {
					ResultSetMetaData metadata =  rs.getMetaData();
					obj = new DashboardObject();
					values = new ArrayList<DashboardValue>();
					for (int x = 0; x < metadata.getColumnCount(); x++) {
						try {
							value = new DashboardValue(metadata.getColumnLabel(x + 1), rs.getObject(x + 1));
						} catch (Exception e) {}
						
						values.add(value);
					}
					obj.setValues(values);
					objs.add(obj);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (con != null)
						con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return objs;
		}
	
	protected List<DashboardObject> proceedQueryMultipleStringData(String jndi, StringBuilder sql) {
    	
		Connection con = null;
		List<DashboardObject> objs = new ArrayList<>();
		try {

			con = ConnectionUtil.getConnection(jndi);
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();

			DashboardObject obj = null;
			List<DashboardValue> values = null;
			DashboardValue value = null;
			while (rs.next()) {
				ResultSetMetaData metadata =  rs.getMetaData();
				obj = new DashboardObject();
				values = new ArrayList<DashboardValue>();
				for (int x = 0; x < metadata.getColumnCount(); x++) {
					try {
						value = new DashboardValue(metadata.getColumnLabel(x + 1), rs.getString(x + 1));
					} catch (Exception e) {}
					
					values.add(value);
				}
				obj.setValues(values);
				objs.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return objs;
	}
	
	
	protected List<DashboardValue> proceedQuerySingle(String jndi, StringBuilder sql) {
		System.out.println("proceedQuerySingle ==> " + sql.toString());
		Connection con = null;
		List<DashboardValue> values = new ArrayList<>();
		try {
	
			con = ConnectionUtil.getConnection(jndi);
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
	
			DashboardValue value = null;
			if (rs.next()) {
				ResultSetMetaData metadata =  rs.getMetaData();
				values = new ArrayList<DashboardValue>();
				for (int x = 0; x < metadata.getColumnCount(); x++) {
					value = new DashboardValue(metadata.getColumnLabel(x + 1), rs.getObject(x + 1));
					values.add(value);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return values;
	}
	
protected int proceedQueryCount(String jndi, StringBuilder sql) {
		
	int test=0;
	Connection con = null;
	try {

		con = ConnectionUtil.getConnection(jndi);
		PreparedStatement ps = con.prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {
			test = rs.getInt(1);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			if (con != null)
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	return test;
	}
	
	public String insertData(String jndi, String tableName, List<DashboardValue> datas){
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO "+tableName);
		String col=" (";
		String values=" VALUES(";
		for(DashboardValue val:datas){
			String value = String.valueOf(val.getValue());
			if(value !=null ){
				if(!value.isEmpty() && !"'null'".equalsIgnoreCase(value)){
					col+=val.getNameValue()+",";
					values+=val.getValue()+",";
				}
			}
		}
		col=col.substring(0, col.length()-1)+") ";
		values=values.substring(0, values.length()-1)+") ";
		sql.append(col);
		sql.append(values);
		System.out.println("SQL: "+ sql);
		return queryExecute(jndi, sql);
	}
	
	public String updateData(String jndi, String tableName, List<DashboardValue> datas, List<DashboardValue> where){
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE "+tableName);
		String set=" SET ";
		for(DashboardValue val:datas){
			String value = String.valueOf(val.getValue());
//			System.out.println("zzzzz " + value);
			if(value !=null ){
				if(!value.isEmpty() && !"'null'".equalsIgnoreCase(value)){
					set+=val.getNameValue()+"="+val.getValue()+" ,";
				}
			}
		}
		set=set.substring(0, set.length()-1)+" ";
		
		String setCondition=" WHERE ";
		for(DashboardValue val:where){
			setCondition+=val.getNameValue()+"="+val.getValue()+" AND";
		}
		setCondition=setCondition.substring(0, setCondition.length()-4)+" ";
		
		sql.append(set);
		sql.append(setCondition);
		System.out.println(sql);
		return queryExecute(jndi, sql);
	}
	
	public String deleteData(String jndi, String tableName, List<DashboardValue> where){
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM "+tableName);
		
		String setCondition=" WHERE ";
		for(DashboardValue val:where){
			setCondition+=val.getNameValue()+"="+val.getValue()+" AND ";
		}
		setCondition=setCondition.substring(0, setCondition.length()-4)+"";
		sql.append(setCondition);
		
		System.out.println("SQL: "+sql);
		return queryExecute(jndi, sql);
	}
	
	public String queryExecute(String jndi, StringBuilder sql) {

		Connection con = null;
		try {

			DataSource ds = getDataSource(jndi);
			con=ds.getConnection();
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.executeUpdate();
			return "success";
		} catch (SQLException e) {
			e.printStackTrace();
			return e.getMessage();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


}
